﻿using System;
using System.Data.SqlClient;
using System.Data;
using Newtonsoft.Json;
using System.Dynamic;
using System.Collections.Generic;
using System.Linq;
using System.Globalization;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Text.Json;

namespace ConsoleApp2
{
    public class CubeQueryResponse
    {
        public CubeQueryResponse()
        {
            ResponseItems = new List<ResponseItem> { };
        }
        public List<ResponseItem> ResponseItems { get; set; }
    }
    public class ResponseItem : IComparable
    {
        public EnumTimeDisplayBy? TimeDisplayType { get; set; }
        public List<ResponseItemField> ResponseItemFields { get; set; }
        public List<ResponseItemField> Groups { get; set; }

        public int CompareTo(object obj)
        {
            if(obj == null)
            {
                return 1;
            }

            if( !(obj is ResponseItem))
            {
                return 1;
            }

            List<ResponseItemField> xa = this.Groups;
            List<ResponseItemField> xb = ((ResponseItem)obj).Groups;
            int r = 0;
            int[] weight = new int[] {10,9,8,7,6,5,4,3,2,1 };
            for (var i = 0; i < xa.Count; i++)
            {
                r += ((xa[i].CompareTo(xb[i]))* weight[i]);
            }


           // Console.WriteLine(r);

            return r;
        }
    }

    public class ResponseItemCompare : Comparer<ResponseItem>
    {
        public override int Compare(ResponseItem x, ResponseItem y)
        {
            if (x == null)
            {
                if (y == null)
                    return 0;
                else
                    return -1;
            }
            else
            {
                if (y == null)
                {
                    return 1;
                }
                else
                {
                    List<ResponseItemField> xa = x.Groups;
                    List<ResponseItemField> xb = y.Groups;
                    int r = 0;

                    for(var i =0; i<xa.Count; i++)
                    {
                        r += (xa[i].CompareTo(xb[i]));
                    }

                    return r;
                }
            }
        }

    }
    public class ResponseItemField : IComparable<ResponseItemField>
    {
        public string Name { get; set; }
        public string Value { get; set; }
        public EnumResponseItemFieldType Type { get; set; }
        public string Expression { get; set; }
        public EnumReportingFieldValueType ValueType { get; set; }

        public int CompareTo(ResponseItemField other)
        {
            if (other == null)
                return 1;
            if (ValueType == EnumReportingFieldValueType.timespan)
            {
                return Convert.ToDateTime(this.Value).CompareTo(Convert.ToDateTime(other.Value));
            }
            else
            {
                return this.Value.CompareTo(other.Value);
            }

        }
    }
    public enum EnumResponseItemFieldType
    {
        field,
        group,
        time,
        calc,
        expression,
    }

    public class ReportingRequestDto
    {
        public List<ReportingRequestCubeEntityDto> CubeEntity { get; set; }
        public List<ReportingRequestFieldDto> Field { get; set; }
        public List<ReportingRequestGroupDto> Group { get; set; }
    }
    public class ReportingRequestCubeEntityDto
    {
        public string Name { get; set; }
        public List<ReportingRequestFilterDto> Filter { get; set; }
    }
    public class ReportingRequestFilterDto
    {
        public string FieldName { get; set; }//syntax {fieldName}
        public EnumMatchType MatchType { get; set; }
        public List<string> Value { get; set; }

        //以下字段方便脚本生成
        public EnumFactFieldType Type { get; set; }
        public string TableName { get; set; }
    }
    public class ReportingRequestFieldDto
    {
        public string Name { get; set; }
        public string CubeEntityName { get; set; }
        public string FieldName { get; set; }//syntax {fieldName}
        public string AggregateCondition { get; set; }
        public EnumReportingFieldType Type { get; set; }
        public string Expression { get; set; }
        public EnumReportingFieldValueType ValueType { get; set; }

        //以下变量方便编程用
        public object DefaultValue { get; set; }//默认值
        public RelatedEntity Related { get; set; }//refence
        public CubeEntity Cube { get; set; }//cube

    }
    public class ReportingRequestGroupDto
    {
        public string Name { get; set; }
        public EnumRowGroupType Type { get; set; }
        public EnumTimeDisplayBy TimeDisplayType { get; set; }
        public List<ReportingRequestGroupFieldDto> Fields { get; set; }
    }
    public class ReportingRequestGroupFieldDto
    {
        public string CubeEntityName { get; set; }
        public string FieldName { get; set; }
    }


    public class ReportingEntity
    {
        public string Name { get; set; }
        public List<ReportingCubeEntity> CubeEntitys { get; set; }
        public List<ReportingField> Fields { get; set; }
        public List<RowGroup> RowGroups { get; set; }
    }
    public class ReportingCubeEntity
    {
        public string Name { get; set; }
        public List<ReportingCubeEntityFilter> Filters { get; set; }
    }
    public class ReportingCubeEntityFilter
    {
        public string Name { get; set; }
        public string FieldName { get; set; }
        public EnumMatchType MatchType { get; set; }
    }


    public class ReportingField
    {
        public string Name { get; set; }
        public string Label { get; set; }
        public EnumReportingFieldValueType ValueType { get; set; }
        public EnumReportingFieldType Type { get; set; }
        public string Expression { get; set; }//字段的计算公式
        //cubeEntity.fieldName
        public string FieldName { get; set; }
        public ReportingFieldCondition Condition { get; set; }
    }
    public class ReportingFieldCondition
    {
        public string Name { get; set; }
        public EnumReportingFieldCondition Operator { get; set; }
        public string Value { get; set; }
    }

    public class RowGroup
    {
        public string Name { get; set; }
        public EnumRowGroupType Type { get; set; }
}

    public class CubeEntity
    {
        public string Name { get; set; }
        //from fact and related
        public List<CubeField> Fields { get; set; }

        public string TableName { get; set; }
    }

    public class CubeField: FactField
    {

    }

    public class FactEntity
    {
        public string Name { get; set; }
        public bool IsSplitTable { get; set; }
        public List<FactField> Fields { get; set; }
    }

    public class FactField
    {
        public string Name { get; set; }
        public EnumFactFieldType Type { get; set; }
        public string ReferenceEntityName { get; set; }
        public int Min { get; set; }
        public int Max { get; set; }
        public int Length { get; set; }
    }

    
    public class RelatedEntity
    {
        public string Name { get; set; }
        public List<RelatedField> Fields { get; set; }

    }
    public class RelatedField
    {
        public string Name { get; set; }
        public EnumFactFieldType Type { get; set; }
        public string ReferenceEntityName { get; set; }
        public bool IsPrimary { get; set; }
    }

    public enum EnumFactFieldType
    {
        @string,
        datetime,
        reference,
        @decimal,
        json,
        @int,
        @bool,
        smallInt,
        timespan,
        guid,
    }
    
    public enum EnumReportingFieldValueType
    {
        number,
        @decimal,
        percent,
        timespan,
    }
    public enum EnumReportingFieldType
    {
        count,
        max,
        average,
        sum,
        expression, 
        value,
    }  
    public enum EnumReportingFieldCondition
    {
        equal,
        notEqual,
    } 
    public enum EnumRowGroupType
    {
        time,
        field,
    } 
    public enum EnumMatchType
    {
        equals,
        notEquals,
        between,
        lessThan,
        greaterThan,
    } 
    public enum EnumTimeDisplayBy
    {
        hour,
        day,
        week,
        month,
        _24x7,
        halfHour,
    } 


    class Program
    {
        //data resource
        static List<FactEntity> factEntities = new List<FactEntity> {
            new FactEntity
            {
                Name = "ManualInvitation",
                Fields = new List<FactField>
                {
                    new FactField
                    {
                        Name = "Datetime",
                        Type = EnumFactFieldType.datetime,
                    },
                    new FactField
                    {
                        Name = "Campaign",
                        Type = EnumFactFieldType.reference,
                        ReferenceEntityName = "Campaign"
                    },
                    new FactField
                    {
                        Name = "Agent",
                        Type = EnumFactFieldType.reference,
                        ReferenceEntityName = "Agent"
                    },
                    new FactField
                    {
                        Name = "IfAccepted",
                        Type = EnumFactFieldType.@bool
                    }
                }
            },
            new FactEntity
            {
                Name = "CannedMessageSendHistory",
                Fields = new List<FactField>
                {
                    new FactField
                    {
                        Name = "Datetime",
                        Type = EnumFactFieldType.datetime,
                    },
                    new FactField
                    {
                        Name = "Campaign",
                        Type = EnumFactFieldType.reference,
                        ReferenceEntityName = "Campaign"
                    },
                    new FactField
                    {
                        Name = "Agent",
                        Type = EnumFactFieldType.reference,
                        ReferenceEntityName = "Agent"
                    },
                    new FactField
                    {
                        Name = "CannedMessageId",
                        Type = EnumFactFieldType.guid
                    }
                }
            },
        };

        static List<RelatedEntity> relatedEntities = new List<RelatedEntity>
        {
            new RelatedEntity
            {
                Name = "Campaign",
                Fields = new List<RelatedField>
                {
                    new RelatedField
                    {
                        Name = "Id",
                        Type = EnumFactFieldType.guid,
                        IsPrimary = true,
                    },
                    new RelatedField
                    {
                        Name = "Name",
                        Type = EnumFactFieldType.@string,
                    },
                }
            },
            new RelatedEntity
            {
                Name = "Agent",
                Fields = new List<RelatedField>
                {
                    new RelatedField
                    {
                        Name = "Id",
                        Type = EnumFactFieldType.guid,
                        IsPrimary = true,
                    },
                    new RelatedField
                    {
                        Name = "Name",
                        Type = EnumFactFieldType.@string,
                    },
                    new RelatedField
                    {
                        Name = "DepartmentId",
                        Type = EnumFactFieldType.reference,
                        ReferenceEntityName = "Department",
                    },
                }
            },
            new RelatedEntity
            {
                Name = "Department",
                Fields = new List<RelatedField>
                {
                    new RelatedField
                    {
                        Name = "Id",
                        Type = EnumFactFieldType.guid,
                        IsPrimary = true,
                    },
                    new RelatedField
                    {
                        Name = "Name",
                        Type = EnumFactFieldType.@string,
                    },
                }
            },
        };

        static List<CubeEntity> cubeEntities
        {
            get
            {
                List<CubeEntity> cubes = new List<CubeEntity> { }; 
                foreach (var factEntitie in factEntities)
                {
                    List<CubeField> cubeFields = new List<CubeField> { };
                    foreach(var field in factEntitie.Fields)
                    {
                        if(field.Type == EnumFactFieldType.reference)
                        {
                            cubeFields.Add(new CubeField
                            {
                                Name = field.Name,
                                Type = field.Type,
                                ReferenceEntityName = field.ReferenceEntityName,
                            });

                            // add reference related entity fields
                            var relatedEntity = relatedEntities.FirstOrDefault(f => f.Name == field.ReferenceEntityName);
                            if (relatedEntity != null
                                && relatedEntity.Fields != null)
                            {
                                foreach(var relatedField in relatedEntity.Fields)
                                {
                                    if (!relatedField.IsPrimary
                                        && relatedField.Type != EnumFactFieldType.reference)
                                    {
                                        cubeFields.Add(new CubeField
                                        {
                                            Name = $"{field.ReferenceEntityName}.{relatedField.Name}",
                                            Type = relatedField.Type,
                                        });
                                    }

                                    //cubeFields.AddRange(relateFields); 不进行递归,只到下探一级
                                }
                            }
                        }
                        else
                        {
                            cubeFields.Add(new CubeField
                            {
                                Name = field.Name,
                                Type = field.Type,
                            });
                        }
                    }

                    cubes.Add(new CubeEntity
                    {
                        Name = factEntitie.Name,
                        Fields = cubeFields,
                    });
                }

                return cubes;
            }
        }

        /// <summary>
        /// 递归调用
        /// </summary>
        /// <param name="relatedField"></param>
        /// <returns></returns>
        static List<CubeField> GetCubeFieldsFromRelated(RelatedField relatedField)
        {
            List<CubeField> cubeFields = new List<CubeField> { };
            cubeFields.Add(new CubeField
            {
                Name = relatedField.Name,
                Type = relatedField.Type,
            });

            if(relatedField.Type == EnumFactFieldType.reference)
            {
                // add reference related entity fields
                var relatedEntity = relatedEntities.FirstOrDefault(f => f.Name == relatedField.ReferenceEntityName);
                if (relatedEntity != null
                    && relatedEntity.Fields != null)
                {
                    foreach (var field in relatedEntity.Fields)
                    {
                        // GetCubeFieldsFromRelated(field); 只需要找一层 所以不需要进行递归调用
                        cubeFields.Add(new CubeField
                        {
                            Name = field.Name,
                            Type = field.Type,
                        });
                    }
                }
            }

            return cubeFields;
        }

        static CubeEntity GetCube(string cubeEntityName)
        {
            return cubeEntities.FirstOrDefault(f=>f.Name == cubeEntityName);
        }

        //public class CubeQueryResponse
        //{
        //    public List<ResponseItem> ResponseItems { get; set; }
        //}
        //public class ResponseItem
        //{
        //    public List<ResponseItemField> ResponseItemFields { get; set; }
        //}
        //public class ResponseItemField
        //{
        //    public string Name { get; set; }
        //    public dynamic Value { get; set; }
        //    public EnumResponseItemField Type { get; set; }

        //}
        //public enum EnumResponseItemField
        //{
        //    field,
        //    group,
        //    clac,
        //    time,
        //}


        public class ReportResultRow : Dictionary<string, object>
        {
            public string GroupKey { get; set; }
            public string SortByTime { get; set; }

            public ReportResultRow() : base()
            {

            }

            public ReportResultRow(string groupKey) : this()
            {
                GroupKey = groupKey;
            }

        }

        public class ReportResultCube
        {
            public string CubeName { get; set; }
            public List<ReportResultRow> Rows { get; set; }

            public ReportResultCube(string cubeName)
            {
                CubeName = cubeName;
                Rows = new List<ReportResultRow> { };
            }
        }

        public class ReportResult 
        {
            public ReportResultRow Total { get; set; }
            public List<ReportResultRow> Series { get; set; }
            public ReportResult()
            {
                Total = new ReportResultRow();
                Series = new List<ReportResultRow> { };
            }
        }

        static void Main(string[] args)
        {

            System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
            stopwatch.Start();

            var datestart = new DateTime(2020, 1, 1, 15, 30, 1);
              var dateend = new DateTime(2020, 1, 9, 15, 31, 1);

            var dicHalfHour = new Dictionary<string, int> { };
            var time = datestart;
            var preTimeDisplay = "";
            while (true)
            {
                var key = $"{time.Hour.ToString("00")}:00 - {time.Hour.ToString("00")}:30";

                if (time.Minute > 30)
                {
                    key = $"{time.Hour.ToString("00")}:30 - {(time.Hour+1).ToString("00")}:00";
                }

                var timeDisplay = $"{time.ToString("yyyy-MM-dd")} {key}";

                if(preTimeDisplay!= timeDisplay)
                {
                    preTimeDisplay = timeDisplay;
                    if (!dicHalfHour.ContainsKey(key))
                    {
                        dicHalfHour.Add(key, 1);
                    }
                    else
                    {
                        dicHalfHour[key] += 1;
                    }
                }

                time = time.AddMinutes(1);
                if (time> dateend)
                {
                    break;
                }
            }
            stopwatch.Stop();

            TimeSpan ts2 = stopwatch.Elapsed;

            stopwatch.Restart();

            var dic7x24 = new Dictionary<string, int> { };
            time = datestart;
            preTimeDisplay = "";
            while (true)
            {
                var key = $"{time.DayOfWeek.ToString().Substring(0,3)}. {time.Hour.ToString("00")}:00 - {(time.Hour + 1).ToString("00")}:00";

                var timeDisplay = $"{time.ToString("yyyy-MM-dd")} {key}";

                if (preTimeDisplay != timeDisplay)
                {
                    preTimeDisplay = timeDisplay;
                    if (!dic7x24.ContainsKey(key))
                    {
                        dic7x24.Add(key, 1);
                    }
                    else
                    {
                        dic7x24[key] += 1;
                    }
                }

                time = time.AddMinutes(1);
                if (time > dateend)
                {
                    break;
                }
            }

            stopwatch.Stop();

            TimeSpan ts3 = stopwatch.Elapsed;

            var serializeOptions = new JsonSerializerOptions
            {
                PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
                DictionaryKeyPolicy = JsonNamingPolicy.CamelCase,
                WriteIndented = true
            };
            var weatherForecast = new Dictionary<string, int> { { "Foo", 1 }, { "Bar", 2 } };
            var jsonString = System.Text.Json.JsonSerializer.Serialize(weatherForecast, serializeOptions);

            CubeQueryResponse queryResponse = new CubeQueryResponse
            {
                ResponseItems = new List<ResponseItem>
                {
                    new ResponseItem
                    {
                        ResponseItemFields=new List<ResponseItemField>
                        {
                            new ResponseItemField
                            {
                                Name = "Sent",
                                Value = "25",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,                                
                            },
                            new ResponseItemField
                            {
                                Name = "Accepted",
                                Value = "15",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "year",
                                Value = "2020",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "month",
                                Value = "01",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "day",
                                Value = "02",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },

                        }, 
                        Groups = new List<ResponseItemField>
                        {
                             new ResponseItemField
                            {
                                Name = "time",
                                Value = "01/02/2020",
                                ValueType =  EnumReportingFieldValueType.timespan,
                            },
                             new ResponseItemField
                            {
                                Name = "AgentId",
                                Value = "a20",
                                ValueType =  EnumReportingFieldValueType.number,
                            },
                        },
                        TimeDisplayType = EnumTimeDisplayBy.day,
                    },
                    new ResponseItem
                    {
                        ResponseItemFields=new List<ResponseItemField>
                        {
                            new ResponseItemField
                            {
                                Name = "Sent",
                                Value = "225",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "Accepted",
                                Value = "115",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "year",
                                Value = "2020",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "month",
                                Value = "01",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "day",
                                Value = "03",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                        },
                        TimeDisplayType = EnumTimeDisplayBy.day,
                        Groups = new List<ResponseItemField>
                        {
                             new ResponseItemField
                            {
                                Name = "time",
                                Value = "01/03/2020",
                                ValueType =  EnumReportingFieldValueType.timespan,
                            }, 
                            new ResponseItemField
                            {
                                Name = "AgentId",
                                Value = "a10",
                                ValueType =  EnumReportingFieldValueType.number,
                            },
                        },
                    },
                    new ResponseItem
                    {
                        ResponseItemFields=new List<ResponseItemField>
                        {
                            new ResponseItemField
                            {
                                Name = "Sent",
                                Value = "15",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "Accepted",
                                Value = "35",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "year",
                                Value = "2019",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "month",
                                Value = "12",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "day",
                                Value = "31",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                        },
                        TimeDisplayType = EnumTimeDisplayBy.day,
                        Groups = new List<ResponseItemField>
                        {
                             new ResponseItemField
                            {
                                Name = "time",
                                Value = "12/31/2019",
                                ValueType =  EnumReportingFieldValueType.timespan,
                            },
                              new ResponseItemField
                            {
                                Name = "AgentId",
                                Value = "a19",
                                ValueType =  EnumReportingFieldValueType.number,
                            },
                        },
                    },
                    new ResponseItem
                    {
                        ResponseItemFields=new List<ResponseItemField>
                        {
                            new ResponseItemField
                            {
                                Name = "Sent",
                                Value = "225",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "Accepted",
                                Value = "115",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "year",
                                Value = "2020",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "month",
                                Value = "01",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "day",
                                Value = "03",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                        },
                        TimeDisplayType = EnumTimeDisplayBy.day,
                        Groups = new List<ResponseItemField>
                        {
                             new ResponseItemField
                            {
                                Name = "time",
                                Value = "01/03/2020",
                                ValueType =  EnumReportingFieldValueType.timespan,
                            },
                            new ResponseItemField
                            {
                                Name = "AgentId",
                                Value = "a30",
                                ValueType =  EnumReportingFieldValueType.number,
                            },
                        },
                    },
                }
            };

            Console.WriteLine("sort before:");

            foreach (var item in queryResponse.ResponseItems)
            {
                Console.WriteLine( $"{ string.Join(',', item.Groups.Select(a => a.Value)) } | { string.Join(',', item.ResponseItemFields.Select(a => a.Value))} ");
            }

            Console.WriteLine("sort after:");

            var sort = queryResponse.ResponseItems.OrderByDescending(a=>a);

            var first = sort.FirstOrDefault();

            foreach (var item in sort)
            {
                Console.WriteLine($"{ string.Join(',', item.Groups.Select(a => a.Value)) } | { string.Join(',', item.ResponseItemFields.Select(a => a.Value))} ");
            }

            Console.WriteLine();

            //var input = new ReportingRequestDto
            //{
            //    CubeEntity = new List<ReportingRequestCubeEntityDto>
            //    {
            //        new ReportingRequestCubeEntityDto
            //        {
            //            Name = "AutoInvitation",
            //            Filter = new List<ReportingRequestFilterDto>
            //            {
            //                new ReportingRequestFilterDto
            //                {
            //                    FieldName = "LogTime",
            //                    MatchType = EnumMatchType.between,
            //                    Value = new List<string>{ "2020/01/02","2020/01/04" },
            //                },
            //            }
            //        },
            //    },
            //    Field = new List<ReportingRequestFieldDto>
            //    {
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "Sent",
            //            CubeEntityName = "AutoInvitation",
            //            FieldName = "SentNum",
            //            AggregateCondition = "",
            //            Type = EnumReportingFieldType.sum
            //        },
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "Accepted",
            //            CubeEntityName = "AutoInvitation",
            //            FieldName = "AcceptNum",
            //            AggregateCondition = "",
            //            Type = EnumReportingFieldType.sum
            //        },
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "Acceptance Rate",
            //            CubeEntityName = "AutoInvitation",
            //            Type = EnumReportingFieldType.expression,
            //            Expression = "Accepted/Sent",
            //            ValueType = EnumReportingFieldValueType.percent,
            //        },
            //    },
            //    Group = new List<ReportingRequestGroupDto>
            //    {
            //        new ReportingRequestGroupDto
            //        {
            //            Name = "time",
            //            Type = EnumRowGroupType.time,
            //            TimeDisplayType = EnumTimeDisplayBy.day,
            //            Fields = new List<ReportingRequestGroupFieldDto>
            //            {
            //                new ReportingRequestGroupFieldDto
            //                {
            //                    CubeEntityName = "AutoInvitation",
            //                    FieldName = "LogTime",
            //                },
            //            },
            //        },
            //    }
            //};
            //ReportResultCube reportResultCube = ParaseCubeQueryResponse("AutoInvitation", queryResponse);
            //ReportResult reportResult = GetCubeGroup("AutoInvitation", new List<ReportResultCube> { reportResultCube }, input);
            //ReportResult reportResult = new ReportResult {
            //   Total = new ReportResultRow
            //   {
            //           { "Sent", 250 },
            //           { "Accepted", 150 },
            //           { "Acceptance Rate", 0.336 },
            //   },
            //   Series = new List<ReportResultRow> { 
            //      new ReportResultRow
            //      {

            //               { "Sent", 20 },
            //               { "Accepted", 10 },
            //               { "Time", "01/03/2020" },

            //      },
            //   new ReportResultRow
            //      {

            //               { "Sent", 20 },
            //               { "Accepted", 10 },
            //               { "Time", "01/03/2020" },

            //      },
            //   new ReportResultRow
            //      {

            //               { "Sent", 20 },
            //               { "Accepted", 10 },
            //               { "Time", "01/03/2020" },

            //      }
            //   }
            //};

            //string reportResultJson = JsonConvert.SerializeObject(reportResult);

            #region
            //string s = "( ax + bx )+(x*y)";

            //var x =Regex.Replace(s, @"[\+ \- \* \/ \( \)]", " ");
            //var xs = x.Split(' ', StringSplitOptions.RemoveEmptyEntries);
            //var sx = Regex.Matches(x, @"(^|[\+ \- \* \/ \( \)])(?<word>[a-zA-Z][a-zA-Z'']*)");

            //string expression = "1+((2+3)*40.5)-5.3";
            ////结果计算
            //var result = PolandNotation.Calculate<float>(expression);
            //Console.WriteLine($"{expression}={result}");

            //var testday = new DateTime(2020, 7, 1);
            //var weekfirst = WeekStartTime(testday);

            //AddMockData();

            //var startDate = DateTime.Parse("2019-12-30");
            //var endDate = DateTime.Parse("2020-01-07");
            //var dic = GetWeekDictionary(startDate, endDate);

            //CubeQueryResponse cubeQueryResponse = new CubeQueryResponse
            //{
            //    ResponseItems = new List<ResponseItem>
            //    {
            //        new ResponseItem
            //        {
            //            ResponseItemFields = new List<ResponseItemField>
            //            {
            //                new ResponseItemField
            //                {
            //                    Name = "AgentId",
            //                    Value = "1",
            //                    Type = EnumResponseItemField.group,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "CampaignId",
            //                    Value = "1",
            //                    Type = EnumResponseItemField.group,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "SendCount",
            //                    Value = 1,
            //                    Type = EnumResponseItemField.clac,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "time",
            //                    Value = "12/30/2019~01/04/2020",
            //                    Type = EnumResponseItemField.time,
            //                },
            //            },
            //        },
            //        new ResponseItem
            //        {
            //            ResponseItemFields = new List<ResponseItemField>
            //            {
            //                new ResponseItemField
            //                {
            //                    Name = "AgentId",
            //                    Value = "1",
            //                    Type = EnumResponseItemField.group,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "CampaignId",
            //                    Value = "1",
            //                    Type = EnumResponseItemField.group,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "SendCount",
            //                    Value = 2,
            //                    Type = EnumResponseItemField.clac,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "time",
            //                    Value = "12/30/2019~01/04/2020",
            //                    Type = EnumResponseItemField.time,
            //                },
            //            },
            //        },
            //        new ResponseItem
            //        {
            //            ResponseItemFields = new List<ResponseItemField>
            //            {
            //                new ResponseItemField
            //                {
            //                    Name = "AgentId",
            //                    Value = "1",
            //                    Type = EnumResponseItemField.group,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "CampaignId",
            //                    Value = "2",
            //                    Type = EnumResponseItemField.group,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "SendCount",
            //                    Value = 1,
            //                    Type = EnumResponseItemField.clac,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "time",
            //                    Value = "12/30/2019~01/04/2020",
            //                    Type = EnumResponseItemField.time,
            //                },
            //            },
            //        },
            //        new ResponseItem
            //        {
            //            ResponseItemFields = new List<ResponseItemField>
            //            {
            //                new ResponseItemField
            //                {
            //                    Name = "AgentId",
            //                    Value = "2",
            //                    Type = EnumResponseItemField.group,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "CampaignId",
            //                    Value = "1",
            //                    Type = EnumResponseItemField.group,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "SendCount",
            //                    Value = 1,
            //                    Type = EnumResponseItemField.clac,
            //                },
            //                new ResponseItemField
            //                {
            //                    Name = "time",
            //                    Value = "01/05/2020~01/07/2020",
            //                    Type = EnumResponseItemField.time,
            //                },
            //            },
            //        },
            //    }
            //};

            //var modelList = new List<dynamic>();
            //Dictionary<string, IDictionary<string, dynamic>> modelDic = new Dictionary<string, IDictionary<string, dynamic>> { };
            //foreach(var responseItem in cubeQueryResponse.ResponseItems)
            //{
            //    dynamic model = new ExpandoObject();
            //    IDictionary<string, dynamic> dict = (IDictionary<string, dynamic>)model;
            //    string key = "";
            //    List<string> clcFields = new List<string> { };
            //    foreach(var field in responseItem.ResponseItemFields)
            //    {
            //        if(field.Type == EnumResponseItemField.group ||
            //            field.Type == EnumResponseItemField.time)
            //        {
            //            key += $"{field.Value}";
            //        }

            //        if(field.Type == EnumResponseItemField.clac)
            //        {
            //            clcFields.Add(field.Name);
            //        }

            //        dict[field.Name] = field.Value;
            //    }

            //    if (modelDic.ContainsKey(key))
            //    {
            //        foreach(var field in clcFields)
            //        {
            //            modelDic[key][field] += dict[field];
            //        }
            //    }
            //    else
            //    {
            //        modelDic.Add(key, dict);
            //        modelList.Add(model);
            //    }


            //}

            //string json = JsonConvert.SerializeObject(modelList);

            //List<Dictionary<string, object>> rows = new List<Dictionary<string, object>> { };
            //rows.Add(new Dictionary<string, object> { { "time", "2020/01/02" } });

            //Dictionary<string,Dictionary<string, object>> data = new Dictionary<string, Dictionary<string, object>> { };
            //data.Add("2020/01/02", new Dictionary<string, object> { { "id", 100 } });

            //List<string> fds = new List<string> { "id", "name" };

            //foreach(Dictionary<string, object> row in rows)
            //{
            //    string time = row["time"].ToString();
            //    if (data.ContainsKey(time))
            //    {
            //        //foreach (string key in data[time].Keys)
            //        //{
            //        //    row.Add(key, data[time][key]);
            //        //}

            //        foreach(string fd in fds)
            //        {
            //            object value = data[time].GetValueOrDefault(fd);
            //            row.Add(fd, value);
            //        }
            //    }
            //    else
            //    {

            //    }
            //}

            //json = JsonConvert.SerializeObject(rows);

            ////数据库的配置数据
            //var reporting = new ReportingEntity
            //{
            //    Name = "Manual Invitation",
            //    CubeEntitys = new List<ReportingCubeEntity>
            //    {
            //        new ReportingCubeEntity
            //        {
            //            Name = "ManualInvitation",
            //            Filters = new List<ReportingCubeEntityFilter>{ 
            //                new ReportingCubeEntityFilter{ 
            //                 Name="Datetime",
            //                 FieldName = "ManualInvitation.Datetime",
            //                 MatchType = EnumMatchType.between,
            //                }
            //            }
            //        },
            //        new ReportingCubeEntity
            //        {
            //            Name = "Agent",
            //            //Filters = new List<ReportingCubeEntityFilter>{
            //            //    new ReportingCubeEntityFilter{
            //            //     Name="",
            //            //     FieldName = "",
            //            //     MatchType = EnumMatchType.equals,
            //            //    }
            //            //}
            //        },
            //        new ReportingCubeEntity
            //        {
            //            Name = "Campaign",
            //            //Filters = new List<ReportingCubeEntityFilter>{
            //            //    new ReportingCubeEntityFilter{
            //            //     Name="",
            //            //     FieldName = "",
            //            //     MatchType = EnumMatchType.equals,
            //            //    }
            //            //}
            //        },
            //        new ReportingCubeEntity
            //        {
            //            Name = "Department",
            //            //Filters = new List<ReportingCubeEntityFilter>{
            //            //    new ReportingCubeEntityFilter{
            //            //     Name="",
            //            //     FieldName = "",
            //            //     MatchType = EnumMatchType.equals,
            //            //    }
            //            //}
            //        },
            //    },
            //    Fields = new List<ReportingField> { 
            //        new ReportingField
            //        {
            //            Name = "Datetime",
            //            Label ="Date Time",
            //            ValueType = EnumReportingFieldValueType.timespan,
            //            Type = EnumReportingFieldType.value,
            //            Expression = "",
            //            FieldName = "ManualInvitation.Datetime",
            //            //Condition = new ReportingFieldCondition
            //            //{
            //            //    Name = "",
            //            //    Operator = EnumReportingFieldCondition.equal,
            //            //    Value = "2002-1-1",
            //            //}
            //        },
            //        new ReportingField
            //        {
            //            Name = "CampaignName",
            //            Label ="Campaign Name",
            //            ValueType = EnumReportingFieldValueType.@float,
            //            Type = EnumReportingFieldType.value,
            //            Expression = "",
            //            FieldName = "Campaign.Name",
            //            //Condition = new ReportingFieldCondition
            //            //{
            //            //    Name = "",
            //            //    Operator = EnumReportingFieldCondition.equal,
            //            //    Value = "",
            //            //}
            //        },
            //        new ReportingField
            //        {
            //            Name = "AgentName",
            //            Label ="Agent Name",
            //            ValueType = EnumReportingFieldValueType.@float,
            //            Type = EnumReportingFieldType.value,
            //            Expression = "",
            //            FieldName = "Agent.Name",
            //            //Condition = new ReportingFieldCondition
            //            //{
            //            //    Name = "",
            //            //    Operator = EnumReportingFieldCondition.equal,
            //            //    Value = "",
            //            //}
            //        },
            //        new ReportingField
            //        {
            //            Name = "DepartmentName",
            //            Label ="Department Name",
            //            ValueType = EnumReportingFieldValueType.@float,
            //            Type = EnumReportingFieldType.value,
            //            Expression = "",
            //            FieldName = "Department.Name",
            //            //Condition = new ReportingFieldCondition
            //            //{
            //            //    Name = "",
            //            //    Operator = EnumReportingFieldCondition.equal,
            //            //    Value = "",
            //            //}
            //        },
            //        new ReportingField
            //        {
            //            Name = "AcceptedCount",
            //            Label ="Accepted Count",
            //            ValueType = EnumReportingFieldValueType.@int,
            //            Type = EnumReportingFieldType.count,
            //            Expression = "count(*)",
            //            FieldName = "ManualInvitation.IfAccepted",
            //            Condition = new ReportingFieldCondition
            //            {
            //                Name = "Accepted",
            //                Operator = EnumReportingFieldCondition.equal,
            //                Value = "true",
            //            }
            //        },
            //    },
            //    RowGroups = new List<RowGroup>
            //    {
            //        //new RowGroup
            //        //{
            //        //    Name = "ManualInvitation.CampaignId",
            //        //    Type = EnumRowGroupType.field,
            //        //},
            //        //new RowGroup
            //        //{
            //        //    Name = "ManualInvitation.AgentId",
            //        //    Type = EnumRowGroupType.field,
            //        //}
            //    }
            //};

            //var input = new ReportingRequestDto
            //{
            //    CubeEntity = new List<ReportingRequestCubeEntityDto>
            //    {
            //        new ReportingRequestCubeEntityDto
            //        {
            //            Name = "ManualInvitation",
            //            Filter = new List<ReportingRequestFilterDto>
            //            {
            //                new ReportingRequestFilterDto
            //                {
            //                    FieldName = "Datetime",
            //                    MatchType = EnumMatchType.between,
            //                    Value = "2020/01/02~2020/03/02",
            //                },
            //                new ReportingRequestFilterDto
            //                {
            //                    FieldName = "IfAccepted",
            //                    MatchType = EnumMatchType.equals,
            //                    Value = "1",
            //                },
            //            }
            //        },
            //        //new ReportingRequestCubeEntityDto
            //        //{
            //        //    Name = "CannedMessageSendHistory",
            //        //    Filter = new List<ReportingRequestFilterDto>
            //        //    {
            //        //        new ReportingRequestFilterDto
            //        //        {
            //        //            FieldName = "Datetime",
            //        //            MatchType = EnumMatchType.between,
            //        //            Value = "2020/01/02~2020/03/02",
            //        //        },
            //        //    }
            //        //},
            //    },
            //    Field = new List<ReportingRequestFieldDto>
            //    {
            //        //new ReportingRequestFieldDto
            //        //{
            //        //    Name = "Datetime",
            //        //    CubeEntityName = "ManualInvitation",
            //        //    FieldName = "Datetime",
            //        //    AggregateCondition = "",
            //        //    Type = EnumReportingFieldType.value 
            //        //},
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "CampaignId",
            //            CubeEntityName = "ManualInvitation",
            //            FieldName = "CampaignId",
            //            AggregateCondition = "",
            //            Type = EnumReportingFieldType.value
            //        },
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "AgentId",
            //            CubeEntityName = "ManualInvitation",
            //            FieldName = "AgentId",
            //            AggregateCondition = "",
            //            Type = EnumReportingFieldType.value
            //        },
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "CampaignName",
            //            CubeEntityName = "ManualInvitation",
            //            FieldName = "Campaign.Name",
            //            AggregateCondition = "",
            //            Type = EnumReportingFieldType.value
            //        },
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "AgentName",
            //            CubeEntityName = "ManualInvitation",
            //            FieldName = "Agent.Name",
            //            AggregateCondition = "",
            //            Type = EnumReportingFieldType.value
            //        },                    
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "AcceptedCount",
            //            CubeEntityName = "ManualInvitation",
            //            FieldName = "IfAccepted",
            //            AggregateCondition = "IfAccepted=1",
            //            Type = EnumReportingFieldType.count
            //        },
            //        new ReportingRequestFieldDto
            //        {
            //            Name = "UsedCount",
            //            CubeEntityName = "CannedMessageSendHistory",
            //            FieldName = "CannedMessageId",//? count(*)
            //            AggregateCondition = "",
            //            Type = EnumReportingFieldType.count
            //        },
            //    },
            //    Group = new List<ReportingRequestGroupDto>
            //    {
            //        new ReportingRequestGroupDto
            //        {
            //            Type = EnumRowGroupType.field,
            //            Fields = new List<ReportingRequestGroupFieldDto>
            //            {
            //                new ReportingRequestGroupFieldDto
            //                {
            //                    CubeEntityName = "ManualInvitation",
            //                    FieldName = "AgentId",
            //                },
            //                new ReportingRequestGroupFieldDto
            //                {
            //                    CubeEntityName = "CannedMessageSendHistory",
            //                    FieldName = "AgentId",
            //                },
            //            },
            //        },
            //        new ReportingRequestGroupDto
            //        {
            //            Type = EnumRowGroupType.field,
            //            Fields = new List<ReportingRequestGroupFieldDto>
            //            {
            //                new ReportingRequestGroupFieldDto
            //                {
            //                    CubeEntityName = "ManualInvitation",
            //                    FieldName = "CampaignId",
            //                },
            //                new ReportingRequestGroupFieldDto
            //                {
            //                    CubeEntityName = "CannedMessageSendHistory",
            //                    FieldName = "CampaignId",
            //                },
            //            },
            //        },
            //        //new ReportingRequestGroupDto
            //        //{
            //        //    Type = EnumRowGroupType.time,
            //        //    TimeDisplayType = EnumTimeDisplayBy._24x7,
            //        //    Fields = new List<ReportingRequestGroupFieldDto>
            //        //    {
            //        //        new ReportingRequestGroupFieldDto
            //        //        {
            //        //            CubeEntityName = "ManualInvitation",
            //        //            FieldName = "Datetime",
            //        //        },
            //        //        new ReportingRequestGroupFieldDto
            //        //        {
            //        //            CubeEntityName = "CannedMessageSendHistory",
            //        //            FieldName = "Datetime",
            //        //        },
            //        //    },
            //        //},
            //    }
            //};

            //var output = GetReporting(input);
            #endregion

            Console.WriteLine("Hello World!");
            Console.ReadKey();
        }
        static CubeQueryResponse QueryCube(ReportingRequestCubeEntityDto cubeEntity, ReportingRequestDto input)
        {
            CubeQueryResponse cubeQueryResponse = new CubeQueryResponse { };
            bool isGroupByTime = false;
            DateTime startDate = DateTime.MinValue;
            DateTime endDate = DateTime.MinValue;
            EnumTimeDisplayBy enumTimeDisplayBy = EnumTimeDisplayBy.day;
            //todo from db cube
            CubeEntity cube = GetCube(cubeEntity.Name);

            //用数据库的cube数据校验传入的参数是否正确，主要校验字段是否存在
            var fields = input.Field.Where(f => f.CubeEntityName == cubeEntity.Name).ToList();
            foreach(var field in fields)
            {
                var cubeField = cube.Fields.FirstOrDefault(f => f.Name == field.FieldName);
                field.DefaultValue = GetDefaultValue(cubeField);
                field.Cube = cube;
            }

            var filter = cubeEntity.Filter;
            filter.ForEach(a => a.TableName = cube.TableName);

            var group = new List<ReportingRequestGroupDto> { };
            if (input.Group != null)
            {
                foreach(var gp in input.Group)
                {
                    if(gp.Type == EnumRowGroupType.time)
                    {
                        if (!isGroupByTime)
                        {
                            isGroupByTime = true;
                            enumTimeDisplayBy = gp.TimeDisplayType;
                        }

                        var groupField = gp.Fields.Where(f => f.CubeEntityName == cubeEntity.Name).FirstOrDefault();
                        var timeFilter = filter.FirstOrDefault(f => f.FieldName == groupField.FieldName && f.MatchType == EnumMatchType.between);
                        if (timeFilter != null)
                        {
                            startDate = DateTime.Parse(timeFilter.Value[0]);
                            endDate = DateTime.Parse(timeFilter.Value[1]);
                        }
                    }

                    group.Add(new ReportingRequestGroupDto
                    {
                        Name = gp.Name,
                        Type = gp.Type,
                        Fields = gp.Fields.Where(f => f.CubeEntityName == cubeEntity.Name).ToList(),
                        TimeDisplayType = gp.TimeDisplayType,
                    });
                }
            }

            //todo from db 
            cubeQueryResponse = new CubeQueryResponse
            {
                ResponseItems = new List<ResponseItem>
                {
                    new ResponseItem
                    {
                        ResponseItemFields=new List<ResponseItemField>
                        {
                            new ResponseItemField
                            {
                                Name = "Sent",
                                Value = "25",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "Accepted",
                                Value = "15",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "year",
                                Value = "2020",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "month",
                                Value = "1",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "day",
                                Value = "2",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                        },
                    },
                    new ResponseItem
                    {
                        ResponseItemFields=new List<ResponseItemField>
                        {
                            new ResponseItemField
                            {
                                Name = "Sent",
                                Value = "25",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "Accepted",
                                Value = "15",
                                Type = EnumResponseItemFieldType.calc,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "year",
                                Value = "2020",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "month",
                                Value = "1",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                            new ResponseItemField
                            {
                                Name = "day",
                                Value = "3",
                                Type = EnumResponseItemFieldType.time,
                                ValueType = EnumReportingFieldValueType.number,
                            },
                        },
                    },
                }
            };

            return cubeQueryResponse;
        }
        static ReportResult GetCubeGroup(string cubeName, List<ReportResultCube> resultCubes, ReportingRequestDto input)
        {
            ReportResult reportResult = new ReportResult { };
            bool isGroupByTime = false;
            DateTime startDate = DateTime.MinValue;
            DateTime endDate = DateTime.MinValue;
            EnumTimeDisplayBy enumTimeDisplayBy = EnumTimeDisplayBy.day;
            if (input.Group != null)
            {
                ReportingRequestGroupDto reportingRequestGroupDto = input.Group.FirstOrDefault(f => f.Type == EnumRowGroupType.time);
                if (reportingRequestGroupDto != null)
                {
                    isGroupByTime = true;
                    enumTimeDisplayBy = reportingRequestGroupDto.TimeDisplayType;
                }
                ReportingRequestGroupFieldDto reportingRequestGroupFieldDto = reportingRequestGroupDto.Fields.FirstOrDefault(f=>f.CubeEntityName == cubeName);
                ReportingRequestFilterDto reportingRequestFilterDto = input.CubeEntity.FirstOrDefault().Filter.FirstOrDefault(f => f.FieldName == reportingRequestGroupFieldDto.FieldName&&f.MatchType == EnumMatchType.between);
                startDate = DateTime.Parse(reportingRequestFilterDto.Value[0]);
                endDate = DateTime.Parse(reportingRequestFilterDto.Value[1]);
            }
            if (isGroupByTime)
            {
                List<string> timeRows = GetGroupByTime(startDate, endDate, enumTimeDisplayBy);
                foreach (var resultCube in resultCubes)
                {//将两个cube合并
                    var cubeFields = input.Field.Where(f => f.CubeEntityName == resultCube.CubeName);
                    foreach (var row in resultCube.Rows)
                    {
                        ReportResultRow seriesFind = reportResult.Series.FirstOrDefault(f => f.GroupKey == row.GroupKey);
                        if (seriesFind != null)
                        {//存在，更新
                            //group
                            foreach (var gp in input.Group)
                            {
                                string fieldName = gp.Name;
                                var value = row[fieldName];
                                seriesFind[fieldName] = value;
                            }
                            //field
                            foreach (var field in cubeFields)
                            {
                                if (field.Type == EnumReportingFieldType.expression)
                                {
                                    //表达式
                                    continue;
                                }
                                string fieldName = field.Name;
                                var value = row[fieldName];
                                seriesFind[fieldName] = value;
                            }
                        }
                        else
                        {
                            string[] keyArr = row.GroupKey.Split(new string[] { GeneralSeparator }, StringSplitOptions.None);
                            foreach(var time in timeRows)
                            {
                                //新启一行，按照time增加行
                                //需要将 row中key的time部分替换掉
                                keyArr[0] = time;
                                string key = string.Join(GeneralSeparator, keyArr);

                                ReportResultRow newRow = new ReportResultRow(key);
                                reportResult.Series.Add(newRow);

                                if(key == row.GroupKey)
                                {
                                    seriesFind = newRow;
                                }

                                //group
                                foreach (var gp in input.Group)
                                {
                                    string fieldName = gp.Name;
                                    var value = row[fieldName];
                                    if (gp.Type == EnumRowGroupType.time)
                                    {
                                        value = time; //time 特殊
                                    }
                                    newRow[fieldName] = value;
                                }
                                //field
                                foreach (var field in cubeFields)
                                {
                                    string fieldName = field.Name;                                    
                                    if(field.Type == EnumReportingFieldType.expression)
                                    {
                                        //表达式
                                        newRow[fieldName] = 0;//默认为0                                        
                                        continue;
                                    }

                                    var value = row[fieldName];
                                    if(row.GroupKey != key)
                                    {
                                        //非本行数据 取默认值
                                        value = field.DefaultValue;
                                    }
                                    newRow[fieldName] = value;
                                }

                            }
                        }

                        //total
                        var calculations = cubeFields.Where(f => f.Type != EnumReportingFieldType.value && f.Type != EnumReportingFieldType.expression);
                        foreach(var calculation in calculations)
                        {
                            //所有的计算列存入total
                            if (!reportResult.Total.ContainsKey(calculation.Name))
                            {
                                reportResult.Total[calculation.Name] = 0;
                            }

                            reportResult.Total[calculation.Name]=Convert.ToDecimal(reportResult.Total[calculation.Name])+ Convert.ToDecimal(row[calculation.Name]);
                        }

                        //表达式，一行数据存储完成后，进行表达式计算
                        var expressions = cubeFields.Where(f => f.Type == EnumReportingFieldType.expression);
                        foreach(var exp in expressions)
                        {
                            var expression = exp.Expression;
                            var expressionReplace = Regex.Replace(expression, @"[\+ \- \* \/ \( \)]", " ");
                            var variableArr = expressionReplace.Split(' ', StringSplitOptions.RemoveEmptyEntries);//找出表达式的变量
                            foreach(var variable in variableArr)
                            {
                                //替换参数变量的数值
                                var variableValue = row[variable];
                                expression = expression.Replace(variable, variableValue.ToString());
                            }
                            //计算表达式值
                            var expressionValue = PolandNotation.Calculate<float>(expression);
                            seriesFind[exp.Name] = expressionValue;

                            //total
                            foreach (var variable in variableArr)
                            {
                                //替换参数变量的数值
                                var variableValue = reportResult.Total[variable];
                                expression = expression.Replace(variable, variableValue.ToString());
                            }
                            //计算表达式值
                            var expressionTotalValue = PolandNotation.Calculate<float>(expression);
                            reportResult.Total[exp.Name] = expressionTotalValue;
                        }
                    }
                }
            }
            return reportResult;
        }
        static string GeneralSeparator = "@";
        static ReportResultCube ParaseCubeQueryResponse(string cubeName, CubeQueryResponse cubeQueryResponse/*, DateTime startDate, DateTime endDate*/)
        {
            ReportResultCube reportResultCube = new ReportResultCube(cubeName);
            List<string> clacFields = new List<string> { };
            foreach(var responseItem in cubeQueryResponse.ResponseItems)
            {
                string time = "";
                string key = "";
                ReportResultRow row = new ReportResultRow { };
                foreach (var field in responseItem.ResponseItemFields)
                {
                    bool isTime = false;
                    if(field.Type == EnumResponseItemFieldType.group)
                    {
                        key = $"{field.Value}{GeneralSeparator}{key}";
                    }

                    if (field.Type == EnumResponseItemFieldType.calc)
                    {
                        clacFields.Add(field.Name);
                    }

                    if (responseItem.TimeDisplayType.HasValue)
                    {
                        switch (responseItem.TimeDisplayType.Value)
                        {
                            case EnumTimeDisplayBy.day:
                                {
                                    if (string.IsNullOrEmpty(time))
                                    {
                                        time = "month/day/year";
                                    }

                                    if(field.Name == "year")
                                    {
                                        var value = field.Value;
                                        time = time.Replace("year", value);
                                        isTime = true;
                                    }
                                    if (field.Name == "month")
                                    {
                                        var value = field.Value;
                                        time = time.Replace("month", value);
                                        isTime = true;
                                    }
                                    if (field.Name == "day")
                                    {
                                        var value = field.Value;
                                        time = time.Replace("day", value);
                                        isTime = true;
                                    }

                                    break;
                                }
                        }
                    }

                    if (!isTime)
                    {
                        row[field.Name] = field.Value;
                    }
                    else
                    {
                        row["time"] = time;//默认添加 time 列
                    }
                }
                key = $"{time}{GeneralSeparator}{key}";// 按照时间分组
                row.GroupKey = key;
                row.SortByTime = time; //时间排序
                reportResultCube.Rows.Add(row);
            }
            return reportResultCube;

        }
        static Dictionary<string,string> GetWeekDictionary(DateTime startDate, DateTime endDate)
        {
            Dictionary<string, string> dic = new Dictionary<string, string> { };
            int day = (endDate - startDate).Days;
            GregorianCalendar gc = new GregorianCalendar(GregorianCalendarTypes.USEnglish);
            int week = 0;
            DateTime currWeekFirstDate = DateTime.MinValue;//本周的第一天
            DateTime currWeekLastDate = DateTime.MinValue;//本周的最后一天
 
            for (int i = 0; i < day; i++)
            {
                DateTime time = startDate.AddDays(i);
                int weekOfYear = gc.GetWeekOfYear(time, CalendarWeekRule.FirstDay, DayOfWeek.Sunday);
                week = (int)gc.GetDayOfWeek(time) + 1;

                if(currWeekFirstDate == DateTime.MinValue)
                {
                    currWeekFirstDate = time;//本周的第一天
                }

                if(currWeekLastDate == DateTime.MinValue)
                {
                    currWeekLastDate = time.AddDays(7 - week);//本周的最后一天
                    if (currWeekLastDate > endDate)
                    {
                        // 查询区间在一周内
                        currWeekLastDate = endDate;
                    }
                }             

                dic[$"{time.Year}{weekOfYear}"] = $"{currWeekFirstDate.ToString("MM/dd/yyyy")} ~ {currWeekLastDate.ToString("MM/dd/yyyy")}";

                if (week == 7)
                {//第二个星期 重新赋值
                    week = 0;
                    currWeekFirstDate = DateTime.MinValue;//本周的第一天
                    currWeekLastDate = DateTime.MinValue;//本周的最后一天
                }
            }            

            return dic;
        }

        /// <summary>
        /// 查询 报表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        static object GetReporting(ReportingRequestDto input)
        {
            //sql

            string sql = "";
            if (input != null
                && input.CubeEntity != null
                && input.CubeEntity.Count > 0
                && input.Field != null
                && input.Field.Count > 0)
            {
                //1.先从数据找出cube信息
                //2.校验输入参数的有效性
                //3.组织SQL
                //4.执行SQL
                //5.将结果集组织成输出对象

                //table 
                Dictionary<string, string> tableDic = new Dictionary<string, string>
                {
                    { "ManualInvitation","T_LiveChat_ManualInvitationStatics" },
                    { "Agent","T_LiveChat_Agent" },
                    { "Campaign","T_LiveChat_CodeGenerationPlan" },
                    { "Department","T_LiveChat_Department" },
                    { "CannedMessageSendHistory","T_Site_CannedMessageUseRecord" },
                };

                string cubsql = "";
                string cubfields = "";
                string cubrelatedrelation = "";

                int index = 1;
                foreach (var cube in input.CubeEntity)
                {
                    Dictionary<string, string> tableAlias = new Dictionary<string, string> { };
                    string alias = "abcdefghijklmnopqrstuvwxyz";//表的别名

                    string cbName = $"cb{index}"; //用来给 cub 起别名
                    index++;

                    string from = "";
                    string where = "";

                    bool isRelatedFull = true; // 比如按照agent分组，fact数据可能缺失部分agent，但需要返回完整的agent数据
                    //List<string> relatedEntitys = new List<string> { }; //用来存储涉及到的 relatedentity
                    string relatedfields = "";
                    string relatedfrom = "";
                    string relatedrelation = ""; // on related.AgentId=tmp.AgentId

                    //一个cube一条sql 对应的一个table
                    CubeEntity cubeEntity = GetCube(cube.Name); //找出cube在数据的配置，用于比较

                    if (cubeEntity != null)
                    {
                        if (tableDic.ContainsKey(cube.Name))
                        {
                            string alia = "";
                            string cubeAlia = "a";
                            if (!tableAlias.ContainsKey(cube.Name))
                            {
                                alia = alias.Substring(0, 1);
                                alias = alias.Remove(0, 1);
                                tableAlias.Add(cube.Name, alia);
                            }
                            else
                            {
                                //错误提示 入参重复的 cube entity
                            }

                            if(string.IsNullOrEmpty(from)
                                && input.Group!=null
                                && input.Group.Count > 0)
                            {
                                //(select campaignid, agentid, cnt = count(IfAccepted) from[dbo].[T_LiveChat_ManualInvitationStatics]
                                //where IfAccepted = 1
                                //group by campaignid,agentid ) tmp

                                from = $"fromgroupslot tmp"; //到时候替换用
                                tableAlias[cube.Name] = "tmp";
                                cubeAlia = "tmp";
                            }
                            else
                            {
                                from = $"{tableDic[cube.Name]} {alia}";
                            }
                            
                            //找出cube里面的reference列
                            var referenceFields = cubeEntity.Fields.Where(f => f.Type == EnumFactFieldType.reference);
                            if(referenceFields!=null
                                && referenceFields.Count() > 0)
                            {
                                string preRelatedAlia = "";
                                foreach (var referenceField in referenceFields)
                                {
                                    var fact = factEntities.FirstOrDefault(f => f.Name == cube.Name);
                                    var factRelatedFiled = fact.Fields.FirstOrDefault(f => f.ReferenceEntityName == referenceField.ReferenceEntityName);
                                    var related = relatedEntities.FirstOrDefault(f => f.Name == factRelatedFiled.ReferenceEntityName);
                                    var relatedKeyField = related.Fields.FirstOrDefault(f => f.IsPrimary);

                                    alia = "";
                                    if (!tableAlias.ContainsKey(related.Name))
                                    {
                                        alia = alias.Substring(0, 1);
                                        alias = alias.Remove(0, 1);
                                        tableAlias.Add(related.Name, alia);
                                    }
                                    else
                                    {
                                        //错误提示 入参重复的 cube entity
                                    }

                                    if (!isRelatedFull)
                                    {// 如果是 包含Related的所有数据则 不需要添加下面的 from 
                                        from += $" inner join {tableDic[related.Name]} {alia} on {cubeAlia}.[{factRelatedFiled.Name}Id]={alia}.[{relatedKeyField.Name}]";
                                    }

                                    if (isRelatedFull)
                                    {
                                        //返回所有的agent
                                        if (string.IsNullOrEmpty(relatedfields))
                                        {
                                            //relatedfields
                                            relatedfields = $"[{related.Name}Id]={alia}.[{relatedKeyField.Name}],[{related.Name}Name]={alia}.[Name]";

                                        }
                                        else
                                        {
                                            relatedfields += $",[{related.Name}Id]={alia}.[{relatedKeyField.Name}],[{related.Name}Name]={alia}.[Name]";
                                        }

                                        if (string.IsNullOrEmpty(relatedfrom))
                                        {
                                            //relatedfrom
                                            relatedfrom = $"{tableDic[related.Name]} {alia}";
                                        }
                                        else
                                        {
                                            relatedfrom += $" full join {tableDic[related.Name]} {alia} on {preRelatedAlia}.[SiteId]={alia}.[SiteId]"; //related 之间用 siteid 关联
                                        }

                                        if (string.IsNullOrEmpty(relatedrelation))
                                        {
                                            //relatedrelation
                                            relatedrelation = $"related.[{related.Name}Id]=tmp.[{related.Name}Id]";
                                        }
                                        else
                                        {
                                            relatedrelation += $" and related.[{related.Name}Id]=tmp.[{related.Name}Id]";
                                        }

                                    }

                                    preRelatedAlia = alia;
                                }
                            }

                            if (cube.Filter != null)
                            {
                                foreach (var filter in cube.Filter)
                                {
                                    var cubeField = cubeEntity.Fields.FirstOrDefault(f => f.Name == filter.FieldName);
                                    bool isValue = IsValueType(cubeField.Type);

                                    string filterString = "";
                                    switch (filter.MatchType)
                                    {
                                        case EnumMatchType.between:
                                            {//datetime from~to
                                                //string[] timespanArr = filter.Value.Split('~');
                                                string fromTime = filter.Value[0];
                                                string toTime = filter.Value[1];
                                                filterString = $"a.[{filter.FieldName}] between '{fromTime}' and '{toTime}'";
                                                if (isValue)
                                                {
                                                    filterString = $"a.[{filter.FieldName}] between {fromTime} and {toTime}";
                                                }
                                                break;
                                            }
                                        case EnumMatchType.equals:
                                            {
                                                filterString = $"a.[{filter.FieldName}] = '{filter.Value}'";
                                                if (isValue)
                                                {
                                                    filterString = $"a.[{filter.FieldName}] = {filter.Value}";
                                                }
                                                break;
                                            }
                                        case EnumMatchType.greaterThan:
                                            {
                                                filterString = $"a.[{filter.FieldName}] > '{filter.Value}'";
                                                if (isValue)
                                                {
                                                    filterString = $"a.[{filter.FieldName}] > {filter.Value}";
                                                }
                                                break;
                                            }
                                        case EnumMatchType.lessThan:
                                            {
                                                filterString = $"a.[{filter.FieldName}] < '{filter.Value}'";
                                                if (isValue)
                                                {
                                                    filterString = $"a.[{filter.FieldName}] < {filter.Value}";
                                                }
                                                break;
                                            }
                                        case EnumMatchType.notEquals:
                                            {
                                                filterString = $"a.[{filter.FieldName}] <> '{filter.Value}'";
                                                if (isValue)
                                                {
                                                    filterString = $"a.[{filter.FieldName}] <> {filter.Value}";
                                                }
                                                break;
                                            }
                                    }

                                    if (string.IsNullOrEmpty(where))
                                    {
                                        where += $" {filterString}";
                                    }
                                    else
                                    {
                                        where += $" and {filterString}";
                                    }
                                }
                            }
                        }
                        else
                        {
                            //错误提示 fact table 不存在
                        }
                    }
                    else
                    {
                        //错误提示 cube entity 不存在
                    }

                    string fields = "";
                    string groupslotfields = "";//只会存在group里面的列+计算列
                    foreach (var field in input.Field)
                    {
                        string cbname = cbName;
                        if (field.CubeEntityName != cube.Name)
                        {//不相等表示为第二个cub
                            cbname = $"cb2";
                        }

                        if (string.IsNullOrEmpty(cubfields))
                        {
                            cubfields += $"[{field.Name}]={cbname}.[{field.Name}]";
                        }
                        else
                        {
                            cubfields += $",[{field.Name}]={cbname}.[{field.Name}]";
                        }

                        if (field.CubeEntityName != cube.Name)
                        {//不是本cube的field不进行参与本次cub脚本
                            continue;
                        }
                        
                        string tableName = field.CubeEntityName;
                        string relatedEntityName = "";
                        string relatedFieldName = "";

                        if (field.FieldName.Contains('.'))
                        {
                            //reference relatedentity
                            string[] referenceArr = field.FieldName.Split('.');
                            relatedEntityName = referenceArr[0];
                            relatedFieldName = referenceArr[1];

                            tableName = relatedEntityName;

                            //var cubeField = cubeEntity.Fields.FirstOrDefault(f => f.Type == EnumFactFieldType.reference && f.Name == field.FieldName);
                            //if (cubeField != null)
                            //{
                            //    tableName = cubeField.ReferenceEntityName;
                            //}

                        }

                        if (tableAlias.ContainsKey(field.CubeEntityName))
                        {
                            string tableAlia = tableAlias[field.CubeEntityName];
                            string columnName = field.FieldName;

                            //判断是否是计算列
                            if (field.Type != EnumReportingFieldType.value)
                            {
                                columnName = field.Name;                                
                            }

                            //判断是否是reference列
                            if (!string.IsNullOrEmpty(relatedFieldName))
                            {
                                columnName = relatedFieldName;
                                tableAlia = tableAlias[relatedEntityName];
                            }

                            if (string.IsNullOrEmpty(fields))
                            {
                                if (isRelatedFull)
                                {       //  只需要返回计算列,related列由 related结果集返回
                                    if (field.Type != EnumReportingFieldType.value)
                                    {
                                        fields += $"[{field.Name}]=tmp.[{columnName}]";
                                    }
                             
                                }
                                else
                                {
                                    fields += $"[{field.Name}]={tableAlia}.[{columnName}]";
                                }
          
                            }
                            else
                            {
                                if (isRelatedFull)
                                {
                                    //  只需要返回计算列,related列由 related结果集返回
                                    if (field.Type != EnumReportingFieldType.value)
                                    {
                                        fields += $",[{field.Name}]=tmp.[{columnName}]";
                                    }
                                }
                                else
                                {
                                    fields += $",[{field.Name}]={tableAlia}.[{columnName}]";
                                }
                        
                            }

                        }
                        else
                        {
                            //错误提示 入参的字段相对应的 fact entity 没有在入参的CubeEntity 列表中
                        }

                        if (input.Group != null
                            && input.Group.Count > 0)
                        {
                            string fieldName = field.Name;

                            // 判断是否是reference的
                            if (!string.IsNullOrEmpty(relatedEntityName))
                            {
                                fieldName = $"{relatedEntityName}Id"; //用 reference的 Id 去判断
                            }

                            // 检测字段是否在 group 中，否则抛异常
                            if (!input.Group.Exists(f => f.Fields.Exists(a=>a.FieldName == fieldName && a.CubeEntityName == cube.Name )))
                            {
                                //错误提示 返回的列不在group中

                            }

                            //判断是否是计算列
                            if(field.Type != EnumReportingFieldType.value)
                            {
                                string calculationField = GetCalculationFieldName(field);

                                if (string.IsNullOrEmpty(groupslotfields))
                                {
                                    groupslotfields = $"{field.Name}={calculationField}";
                                }
                                else
                                {
                                    groupslotfields += $",{field.Name}={calculationField}";
                                }
                            }
                        }

                    }

                    string group = "";
                    if (input.Group != null
                        && input.Group.Count > 0)
                    {
                        foreach(var gp in input.Group)
                        {

                            if(gp.Fields.Count != input.CubeEntity.Count)
                            {
                                //错误提示 分组列不是在每个cub中存在
                            }

                            string groupField = gp.Fields.FirstOrDefault(f => f.CubeEntityName == cube.Name).FieldName;
                            string groupslotfield = groupField;
                            string field = ""; //如果是按照time进行分组，需要将分组的数据也返回到 上层去 

                            if (cubeEntity.Fields.Exists(f => f.Name == groupField))
                            {
                                //错误提示 分组列不在cub中存在
                            }

                            if (gp.Type == EnumRowGroupType.time)
                            {
                                switch (gp.TimeDisplayType)
                                {
                                    case EnumTimeDisplayBy.day:
                                        {
                                            groupslotfield = $"datepart(year, [{groupField}]) as year,datepart(month, [{groupField}]) as month,datepart(day, [{groupField}]) as day";
                                            groupField = $"datepart(year, [{groupField}]),datepart(month, [{groupField}]),datepart(day, [{groupField}])";
                                            field = $"[year]=tmp.[year],[month]=tmp.[month],[day]=tmp.[day]";
                                            break;
                                        }
                                    case EnumTimeDisplayBy.halfHour:
                                        {
                                            groupslotfield = $"case when datepart (mi,[{groupField}]) < 30 then datepart (hour,[{groupField}])+0.1 else datepart (hour,logTime)+0.2 end as time";
                                            groupField = $"case when datepart (mi,[{groupField}]) < 30 then datepart (hour,[{groupField}])+0.1 else datepart (hour,logTime)+0.2 end";
                                            field = $"[time]=tmp.[time]";
                                            break;
                                        }
                                    case EnumTimeDisplayBy.hour:
                                        {
                                            groupslotfield = $"datepart(year, [{groupField}]) as year,datepart(month, [{groupField}]) as month,datepart(day, [{groupField}]) as day,datepart(hour, [{groupField}]) as hour";
                                            groupField = $"datepart(year, [{groupField}]),datepart(month, [{groupField}]),datepart(day, [{groupField}]),datepart(hour, [{groupField}])";
                                            field = $"[year]=tmp.[year],[month]=tmp.[month],[day]=tmp.[day],[hour]=tmp.[hour]";
                                            break;
                                        }
                                    case EnumTimeDisplayBy.month:
                                        {
                                            groupslotfield = $"datepart(year, [{groupField}]) as year,datepart(month, [{groupField}]) as month";
                                            groupField = $"datepart(year, [{groupField}]),datepart(month, [{groupField}])";
                                            field = $"[year]=tmp.[year],[month]=tmp.[month]";
                                            break;
                                        }
                                    case EnumTimeDisplayBy.week:
                                        {
                                            groupslotfield = $"datepart(year, [{groupField}]) as year,datepart(week, [{groupField}]) as week";
                                            groupField= $"datepart(year, [{groupField}]),datepart(week, [{groupField}])";
                                            field = $"[year]=tmp.[year],[week]=tmp.[week]";
                                            break;
                                        }
                                    case EnumTimeDisplayBy._24x7:
                                        {
                                            groupslotfield = $"datepart(dw, [{groupField}]) as week,datepart(hour, [{groupField}]) as hour";
                                            groupField= $"datepart(dw, [{groupField}]),datepart(hour, [{groupField}])";
                                            field = $"[week]=tmp.[week],[hour]=tmp.[hour]";
                                            break;
                                        }
                                }

                                //group by time 默认将 group 添加到 上层列 返回
                                if (string.IsNullOrEmpty(fields))
                                {
                                    fields += $"{field}";
                                }
                                else
                                {
                                    fields += $",{field}";
                                }
                            }

                            if (string.IsNullOrEmpty(group))
                            {
                                group = $"{groupField}";
                            }
                            else
                            {
                                group += $",{groupField}";
                            }

                            if (string.IsNullOrEmpty(groupslotfields))
                            {
                                groupslotfields = $"{groupslotfield}";
                            }
                            else
                            {
                                groupslotfields += $",{groupslotfield}";
                            }

                            if (index > 2 
                                && gp.Type == EnumRowGroupType.field) //排除掉time,两个cub之间用filed关联
                            {//暂支持最多两个 cube
                                if (string.IsNullOrEmpty(cubrelatedrelation))
                                {
                                    cubrelatedrelation = $"cb1.[{groupField}]= cb2.[{groupField}]";
                                }
                                else
                                {
                                    cubrelatedrelation += $" and cb1.[{groupField}]= cb2.[{groupField}]";
                                }
                            }               
                            
                        }

                        string fromgroupslot = $"(select {groupslotfields} from {tableDic[cubeEntity.Name]} a where {where} group by {group})";
                        from = from.Replace("fromgroupslot", fromgroupslot);
                        sql = $"select {fields} from {from}";
                        
                        if (isRelatedFull)
                        {
                            //
                            sql = $"select related.*,{fields} from ( select {relatedfields} from {relatedfrom} ) related left join {from} on {relatedrelation}";
                        }

                    }
                    else
                    {
                        sql = $"select {fields} from {from} where {where}";
                    }

                    if (index > 2)
                    {
                        //2个cub
                        cubsql += $" full join ({sql}) cb2 on {cubrelatedrelation}";
                    }
                    else
                    {//1个cub
                        cubsql = $"select {cubfields} from ({sql}) cb1";
                    }
           
                }

                //执行 cub sql

                DataTable table = new DataTable
                {

                };
                table.Columns.AddRange(new DataColumn[] {
                        new DataColumn("Datetime",typeof(DateTime)),
                        new DataColumn("CampaignId",typeof(Guid)),
                        new DataColumn("AgentId",typeof(Guid)),
                        new DataColumn("IfAccepted",typeof(bool)),
                        new DataColumn("CampaignName",typeof(string)),
                        new DataColumn("AgentName",typeof(string))
                    });
                table.Rows.Add(new object[] { "2020-10-1 10:10:10", Guid.NewGuid(), Guid.NewGuid(), true, "CampaignName-1", "AgentName-1" });
                table.Rows.Add(new object[] { "2020-10-1 10:10:10", Guid.NewGuid(), Guid.NewGuid(), true, "CampaignName-1", "AgentName-1" });
                table.Rows.Add(new object[] { "2020-10-1 10:10:10", Guid.NewGuid(), Guid.NewGuid(), true, "CampaignName-1", "AgentName-1" });
                table.Rows.Add(new object[] { "2020-10-1 10:10:10", Guid.NewGuid(), Guid.NewGuid(), true, "CampaignName-1", "AgentName-1" });
                table.Rows.Add(new object[] { "2020-10-1 10:10:10", Guid.NewGuid(), Guid.NewGuid(), true, "CampaignName-1", "AgentName-1" });
                table.Rows.Add(new object[] { "2020-10-1 10:10:10", Guid.NewGuid(), Guid.NewGuid(), true, "CampaignName-1", "AgentName-1" });

                dynamic reportObject = new ExpandoObject();
                reportObject.total = new { };//计算字段 汇总
                reportObject.series = table.ToList();
                string json = JsonConvert.SerializeObject(reportObject);

                return json;

            }

            return null;
        }
        static bool IsValueType(EnumFactFieldType fieldType)
        {
            bool bResult = false;
            switch (fieldType)
            {
                case EnumFactFieldType.@bool:
                case EnumFactFieldType.@decimal:
                case EnumFactFieldType.@int:
                case EnumFactFieldType.smallInt:
                    {
                        bResult = true;
                        break;
                    }
            }
            return bResult;
        }
        static string GetCalculationFieldName(ReportingRequestFieldDto field)
        {
            string calculationField = "";
            //判断是否是计算列
            if (field.Type != EnumReportingFieldType.value)
            {
                switch (field.Type)
                {
                    case EnumReportingFieldType.sum:
                        {
                            calculationField = $"sum({field.FieldName})";
                            break;
                        }
                    case EnumReportingFieldType.count:
                        {
                            calculationField = $"count({field.FieldName})";
                            break;
                        }
                    case EnumReportingFieldType.average:
                        {
                            calculationField = $"average({field.FieldName})";
                            break;
                        }
                    case EnumReportingFieldType.max:
                        {
                            calculationField = $"max({field.FieldName})";
                            break;
                        }
                    case EnumReportingFieldType.expression:
                        {
                            calculationField = $"{field.AggregateCondition}";
                            break;
                        }
                }
            }
            return calculationField;
        }
        static object GetDefaultValue(CubeField field)
        {
            object obj = null;
            switch (field.Type)
            {
                case EnumFactFieldType.@bool: { obj = false; break; }
                case EnumFactFieldType.datetime: { obj = ""; break; }
                case EnumFactFieldType.@decimal: { obj = 0; break; }
                case EnumFactFieldType.guid: { obj = ""; break; }
                case EnumFactFieldType.@int: { obj = 0; break; }
                case EnumFactFieldType.smallInt: { obj = 0; break; }
                case EnumFactFieldType.json: { obj = ""; break; }
                case EnumFactFieldType.@string: { obj = ""; break; }
                case EnumFactFieldType.timespan: { obj = ""; break; }
                case EnumFactFieldType.reference: { obj = ""; break; }
            }
            return obj;
        }
        static List<string> GetGroupByTime(DateTime startDate, DateTime endDate, EnumTimeDisplayBy enumTimeDisplayBy)
        {
            List<string> list = new List<string> { };
            switch (enumTimeDisplayBy)
            {
                case EnumTimeDisplayBy.day:
                    {//07/20/2020
                        int index = 0;
                        while (true)
                        {
                            DateTime date = endDate.AddDays(-index);
                            list.Add($"{date.ToString("MM/dd/yyyy")}");
                            index++;
                            if(date.Year == startDate.Year
                                && date.Month == startDate.Month
                                && date.Day == startDate.Day)
                            {
                                break;
                            }
                        }
                        break;
                    }
            }
            return list;
        }
        /*
                static void AddMockData()
                {            
                    DataTable table = new DataTable();
                    table.Columns.AddRange(new DataColumn[] {
                        new DataColumn("ID",typeof(int)),
                        new DataColumn("CodePlanId",typeof(Guid)),
                        new DataColumn("AutoInvitationId",typeof(Guid)),
                        new DataColumn("SentNum",typeof(int)),
                        new DataColumn("AcceptNum",typeof(int)),
                        new DataColumn("RefuseNum",typeof(int)),
                        new DataColumn("LogTime",typeof(DateTime)),
                    });

                    //codegenerationplan D14E32CD-AF53-47A5-88AE-A7DDD5A1ABFD
                    //autoinvitation EA7E5D3C-E486-4CDB-8B61-A71A67100657

                    var index = 0;
                    int count = 30000000;//3000w 30000000
                    int store = 1000000;//每100w存储
                    DateTime startDate = DateTime.UtcNow.AddYears(-1);//last year
                    DateTime endDate = DateTime.UtcNow;

                    System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
                    stopwatch.Start();

                    while (true)
                    {
                        Random random = new Random(Guid.NewGuid().GetHashCode());
                        var sentNum = random.Next(60, 100);
                        var acceptNum = random.Next(1, 60);
                        var refuseNum = sentNum - acceptNum;
                        var codegenerationplan = Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABFD");
                        var autoinvitation = Guid.Parse("EA7E5D3C-E486-4CDB-8B61-A71A67100657");

                        var logTime = startDate.AddMilliseconds(950);
                        startDate = logTime;

                        DataRow dataRow = table.NewRow();

                        dataRow["CodePlanId"] = codegenerationplan;
                        dataRow["AutoInvitationId"] = autoinvitation;
                        dataRow["SentNum"] = sentNum;
                        dataRow["AcceptNum"] = acceptNum;
                        dataRow["RefuseNum"] = refuseNum;
                        dataRow["LogTime"] = logTime;

                        table.Rows.Add(dataRow);

                        //table.Rows.Add(new object[]
                        //{
                        //    codegenerationplan,
                        //    autoinvitation,
                        //    sentNum,
                        //    acceptNum,
                        //    refuseNum,
                        //    logTime
                        //});

                        index++;

                        if(index> count)
                        {
                            break;
                        }

                        if (index % store == 0)
                        { //每100w存储
                            SqlBulkCopy(table);
                            table.Rows.Clear();
                        }

                        Console.WriteLine($"count:{count},current:{index},percent:{(index*100.00 / count*1.00)}%");

                    }

                    stopwatch.Stop();
                    var createTable = stopwatch.ElapsedMilliseconds;
                    Console.WriteLine($"createTable:{createTable} ms");

                }
                static void SqlBulkCopy(DataTable table)
                {
                    var cons = "Data Source=.;Initial Catalog=Test;User ID=sa;Password=Aa000000;";

                    System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
                    stopwatch.Start();

                    using (var conn = new SqlConnection(cons))
                    {
                        var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn)
                        {
                            DestinationTableName = "T_LiveChat_AutoInvitationStatistics3000w",
                            BatchSize = table.Rows.Count
                        };
                        conn.Open();
                        bulkCopy.WriteToServer(table);
                    }

                    stopwatch.Stop();
                    var sqlBulkCopy = stopwatch.ElapsedMilliseconds;
                    Console.WriteLine($"sqlBulkCopy:{sqlBulkCopy} ms");

                }*/
        /*
static void AddMockData()
{
    DataTable table = new DataTable();
    table.Columns.AddRange(new DataColumn[] {
        new DataColumn("ID",typeof(int)),
        new DataColumn("SiteID",typeof(int)),
        new DataColumn("CodePlanId",typeof(Guid)),
        new DataColumn("AutoInvitationId",typeof(Guid)),
        new DataColumn("SentNum",typeof(int)),
        new DataColumn("AcceptNum",typeof(int)),
        new DataColumn("RefuseNum",typeof(int)),
        new DataColumn("LogTime",typeof(DateTime)),
        new DataColumn("TimeHour",typeof(string)),
        new DataColumn("TimeDay",typeof(string)),
        new DataColumn("TimeMonth",typeof(string)),
        new DataColumn("TimeWeek",typeof(string)),
    });

    //codegenerationplan D14E32CD-AF53-47A5-88AE-A7DDD5A1ABFD
    //autoinvitation EA7E5D3C-E486-4CDB-8B61-A71A67100657

    var index = 0;
    int count = 30000000;//3000w 30000000
    int store = 1000000;//每100w存储 1000000
    DateTime startDate = DateTime.UtcNow.AddYears(-1);//last year
    DateTime endDate = DateTime.UtcNow;

    System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
    stopwatch.Start();

    //5
    var codegenerationplans = new Guid[] {
        Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF0"),
        Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF1"),
        Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF2"),
        Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF3"),
        Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF4"),
        //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF5"),
        //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF6"),
        //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF7"),
        //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF8"),
        //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF9"),
    };
    //5
    var autoinvitations = new Guid[] {
        Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF0"),
        Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF1"),
        Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF2"),
        Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF3"),
        Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF4"),
        //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF5"),
        //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF6"),
        //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF7"),
        //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF8"),
        //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF9"),
    };
    //5
    var siteIds = new int[] {
        10000,
        10001,
        10002,
        10003,
        10004,
        //10005,
        //10006,
        //10007,
        //10008,
        //10009,
    };

    while (true)
    {
        Random random = new Random(Guid.NewGuid().GetHashCode());
        var sentNum = random.Next(60, 100);
        var acceptNum = random.Next(1, 60);
        var refuseNum = sentNum - acceptNum;
        var codegenerationplan = codegenerationplans[random.Next(0, 5)];
        var autoinvitation = autoinvitations[random.Next(0, 5)];
        var siteId = siteIds[random.Next(0, 5)];

        var logTime = startDate.AddMilliseconds(950);
        startDate = logTime;

        DataRow dataRow = table.NewRow();

        dataRow["SiteID"] = siteId;
        dataRow["CodePlanId"] = codegenerationplan;
        dataRow["AutoInvitationId"] = autoinvitation;
        dataRow["SentNum"] = sentNum;
        dataRow["AcceptNum"] = acceptNum;
        dataRow["RefuseNum"] = refuseNum;
        dataRow["LogTime"] = logTime;

        dataRow["TimeHour"] = $"{logTime.ToString("MM/dd/yyyy HH:00")}~{logTime.AddHours(1).ToString("HH:00")}";//07/01/2020 23:00~24:00
        dataRow["TimeDay"] = $"{logTime.ToString("MM/dd/yyyy")}";//07/01/2020
        dataRow["TimeMonth"] = $"{logTime.ToString("yyyy-MM")}";//2020-07
        var weekFirstDay = WeekStartTime(logTime);
        dataRow["TimeWeek"] =$"{weekFirstDay.ToString("MM/dd/yyyy")}~{weekFirstDay.AddDays(7).ToString("MM/dd/yyyy")}";//07/01/2020~07/01/2020

        table.Rows.Add(dataRow);

        //table.Rows.Add(new object[]
        //{
        //    codegenerationplan,
        //    autoinvitation,
        //    sentNum,
        //    acceptNum,
        //    refuseNum,
        //    logTime
        //});

        index++;

        if (index > count)
        {
            break;
        }

        if (index % store == 0)
        { //每100w存储
            SqlBulkCopy(table);
            table.Rows.Clear();
        }

        Console.WriteLine($"count:{count},current:{index},percent:{(index * 100.00 / count * 1.00)}%");

    }

    stopwatch.Stop();
    var createTable = stopwatch.ElapsedMilliseconds;
    Console.WriteLine($"createTable:{createTable} ms");

}
public static DateTime WeekStartTime(DateTime now)
{
        DateTime dt = now;
        int dayOfWeek = -1 * (int)dt.Date.DayOfWeek;
        //Sunday = 0,Monday = 1,Tuesday = 2,Wednesday = 3,Thursday = 4,Friday = 5,Saturday = 6,

        DateTime weekStartTime = dt.AddDays(dayOfWeek);//取本周一
        //if (dayOfWeek == 0) //如果今天是周日，则开始时间是上周一
        //{
        //    weekStartTime = weekStartTime.AddDays(-7);
        //}

        return weekStartTime.Date;

}
static void SqlBulkCopy(DataTable table)
{
    var cons = "Data Source=.;Initial Catalog=Test;User ID=sa;Password=Aa000000;";

    System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
    stopwatch.Start();

    using (var conn = new SqlConnection(cons))
    {
        var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn)
        {
            DestinationTableName = "T_LiveChat_AutoInvitationStatistics3001w",
            BatchSize = table.Rows.Count
        };
        conn.Open();
        bulkCopy.WriteToServer(table);
    }

    stopwatch.Stop();
    var sqlBulkCopy = stopwatch.ElapsedMilliseconds;
    Console.WriteLine($"sqlBulkCopy:{sqlBulkCopy} ms");

}
*/
        static void AddMockData()
        {
            DataTable table = new DataTable();
            table.Columns.AddRange(new DataColumn[] {
                new DataColumn("ID",typeof(int)),
                new DataColumn("SiteID",typeof(int)),
                new DataColumn("CodePlanId",typeof(Guid)),
                new DataColumn("AutoInvitationId",typeof(Guid)),
                new DataColumn("SentNum",typeof(int)),
                new DataColumn("AcceptNum",typeof(int)),
                new DataColumn("RefuseNum",typeof(int)),
                new DataColumn("LogTime",typeof(DateTime)),
                new DataColumn("TimeHour",typeof(string)),
                new DataColumn("TimeDay",typeof(string)),
                new DataColumn("TimeMonth",typeof(string)),
                new DataColumn("TimeWeek",typeof(string)),
            });

            //codegenerationplan D14E32CD-AF53-47A5-88AE-A7DDD5A1ABFD
            //autoinvitation EA7E5D3C-E486-4CDB-8B61-A71A67100657

            var index = 0;
            int count = 30000000;//3000w 30000000
            int store = 1000000;//每100w存储 1000000
            DateTime startDate = DateTime.UtcNow.AddYears(-1);//last year
            DateTime endDate = DateTime.UtcNow;

            System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
            stopwatch.Start();

            //5
            var codegenerationplans = new Guid[] {
                Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF0"),
                Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF1"),
                Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF2"),
                Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF3"),
                Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF4"),
                //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF5"),
                //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF6"),
                //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF7"),
                //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF8"),
                //Guid.Parse("D14E32CD-AF53-47A5-88AE-A7DDD5A1ABF9"),
            };
            //5
            var autoinvitations = new Guid[] {
                Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF0"),
                Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF1"),
                Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF2"),
                Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF3"),
                Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF4"),
                //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF5"),
                //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF6"),
                //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF7"),
                //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF8"),
                //Guid.Parse("A14E32CD-AF53-47A5-88AE-A7DDD5A1ABF9"),
            };
            //5
            var siteIds = new int[] {
                10000,
                10001,
                10002,
                10003,
                10004,
                //10005,
                //10006,
                //10007,
                //10008,
                //10009,
            };

            while (true)
            {
                Random random = new Random(Guid.NewGuid().GetHashCode());
                var sentNum = random.Next(60, 100);
                var acceptNum = random.Next(1, 60);
                var refuseNum = sentNum - acceptNum;
                var codegenerationplan = codegenerationplans[random.Next(0, 5)];
                var autoinvitation = autoinvitations[random.Next(0, 5)];
                var siteId = siteIds[random.Next(0, 5)];

                var logTime = startDate.AddMilliseconds(950);
                startDate = logTime;

                DataRow dataRow = table.NewRow();

                dataRow["SiteID"] = siteId;
                dataRow["CodePlanId"] = codegenerationplan;
                dataRow["AutoInvitationId"] = autoinvitation;
                dataRow["SentNum"] = sentNum;
                dataRow["AcceptNum"] = acceptNum;
                dataRow["RefuseNum"] = refuseNum;
                dataRow["LogTime"] = logTime;

                dataRow["TimeHour"] = $"{logTime.ToString("MM/dd/yyyy HH:00")}~{logTime.AddHours(1).ToString("HH:00")}";//07/01/2020 23:00~24:00
                dataRow["TimeDay"] = $"{logTime.ToString("MM/dd/yyyy")}";//07/01/2020
                dataRow["TimeMonth"] = $"{logTime.ToString("yyyy-MM")}";//2020-07
                var weekFirstDay = WeekStartTime(logTime);
                dataRow["TimeWeek"] = $"{weekFirstDay.ToString("MM/dd/yyyy")}~{weekFirstDay.AddDays(7).ToString("MM/dd/yyyy")}";//07/01/2020~07/01/2020

                table.Rows.Add(dataRow);

                //table.Rows.Add(new object[]
                //{
                //    codegenerationplan,
                //    autoinvitation,
                //    sentNum,
                //    acceptNum,
                //    refuseNum,
                //    logTime
                //});

                index++;

                if (index > count)
                {
                    break;
                }

                if (index % store == 0)
                { //每100w存储
                    SqlBulkCopy(table);
                    table.Rows.Clear();
                }

                Console.WriteLine($"count:{count},current:{index},percent:{(index * 100.00 / count * 1.00)}%");

            }

            stopwatch.Stop();
            var createTable = stopwatch.ElapsedMilliseconds;
            Console.WriteLine($"createTable:{createTable} ms");

        }
        public static DateTime WeekStartTime(DateTime now)
        {
            DateTime dt = now;
            int dayOfWeek = -1 * (int)dt.Date.DayOfWeek;
            //Sunday = 0,Monday = 1,Tuesday = 2,Wednesday = 3,Thursday = 4,Friday = 5,Saturday = 6,

            DateTime weekStartTime = dt.AddDays(dayOfWeek);//取本周一
                                                           //if (dayOfWeek == 0) //如果今天是周日，则开始时间是上周一
                                                           //{
                                                           //    weekStartTime = weekStartTime.AddDays(-7);
                                                           //}

            return weekStartTime.Date;

        }
        static void SqlBulkCopy(DataTable table)
        {
            var cons = "Data Source=.;Initial Catalog=Test;User ID=sa;Password=Aa000000;";

            System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
            stopwatch.Start();

            using (var conn = new SqlConnection(cons))
            {
                var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn)
                {
                    DestinationTableName = "T_LiveChat_AutoInvitationStatistics3002w_nocolumnstore",
                    BatchSize = table.Rows.Count
                };
                conn.Open();
                bulkCopy.WriteToServer(table);
            }

            stopwatch.Stop();
            var sqlBulkCopy = stopwatch.ElapsedMilliseconds;
            Console.WriteLine($"sqlBulkCopy:{sqlBulkCopy} ms");

        }
    }
}
